# coding:utf-8
import sqlite3
# db=DataBase=数据库
import pymysql
from enum import Enum
"""
操作msql和sqlite的数据库封装
"""
MYSQL_COF = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': 'root',
    'database': 'db',
    'charset': 'utf8'
}
SQLITE_COF = {
    'database': 'db.sqlite'
}


class DB_type(Enum):
    MYSQL = 'mysql'
    SQLITE = 'sqlite'


class DBTool:

    def __init__(self, db_type: DB_type = DB_type.MYSQL):

        # 提示用户输入正确的数据库类型
        if not isinstance(db_type, DB_type):
            raise TypeError("参数类型不对,请使用'DB_type.MYSQL'的形式")
        if db_type.value == 'mysql':
            con = pymysql.connect(**MYSQL_COF)
        elif db_type.value == 'sqlite':
            con = sqlite3.connect(**SQLITE_COF)
        self.__con = con
        self.__cursor = self.__con.cursor()

    def close_all(self):
        print("关闭游标和连接对象")
        self.__cursor.close()
        self.__con.close()
        self.__cursor = None
        self.__con = None

    def __del__(self):
        try:
            self.close_all()
        except Exception as e:
            pass

    def create_table(self, sql: str) -> None:
        self.__cursor.execute(sql)
        print("创建表成功")

    def drop_table(self, table_name: str) -> None:
        # 删除表要慎重
        # 1）表是否存在  2）表中的数据怎么办
        sql = f"drop table IF EXISTS {table_name}"
        self.__cursor.execute(sql)
        self.__con.commit()
        print("删除表成功")

    # lazyload=懒加载=用到的时候再创建
    def insert(self, sql: str, params: list = None) -> None:
        for param in params:
            self.__cursor.execute(sql, param)
            self.__con.commit()
            print("插入成功")

    def delete(self, sql: str) -> None:
        self.__cursor.execute(sql)
        self.__con.commit()

    def update(self, sql: str, param: tuple = None) -> None:
        self.__cursor.execute(sql, param)
        self.__con.commit()

    def fetchone(self, sql: str, param: tuple = None) -> tuple:
        self.__cursor.execute(sql, param)
        return self.__cursor.fetchone()

    def fetchall(self, sql: str, param: tuple = None) -> list:
        self.__cursor.execute(sql, param)
        return self.__cursor.fetchall()


if __name__ == '__main__':
    db = DBTool(DB_type.MYSQL)
    db.create_table("""CREATE TABLE IF NOT EXISTS a(id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20))""")
    # db.drop_table("a")
    # db.drop_table("b")
    db.insert("insert into a (name) values (%s)", (("张三",),))
    db.insert("insert into a (name) values (%s)", (("李四",), ("王五",)))
    db.delete("delete from a where name like '张%'")
    db.update("update a set name=%s where id>1", ('张三',))
    db.fetchone("select * from a where name=%s", ('张三',))
    db.fetchall("select * from a where name=%s", ('张三',))
    db = DBTool(DB_type.SQLITE)
    db.create_table("""CREATE TABLE IF NOT EXISTS a(id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(20))""")
    # db.drop_table("a")
    # db.drop_table("b")
    db.insert("insert into a (name) values (?)", (("张三",),))
    db.insert("insert into a (name) values (?)", (("李四",), ("王五",)))
    db.delete("delete from a where name like '张%'")
    db.update("update a set name=? where id>1", ('张三',))
    a = db.fetchone("select * from a where name=?", ('张三',))
    b = db.fetchall("select * from a where name=?", ('张三',))
